package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.ICountProTransService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("countProTransService")
public class CountProTransService implements ICountProTransService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");
        final String recordName = "tb_statis_sendclear_intf";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
            XxlJobLogger.log("[普通]本次门架流水最大记录RECORDID为:" + recordStr);

            String dbCurTime = DBUtils.getDBCurTime(connPre);
            XxlJobLogger.log("[普通]本次同步截止时间为:" + dbCurTime);

            StringBuffer sql = new StringBuffer();
            sql.append("insert into TB_STATIS_SENDCLEAR_INTF_TMP(SEQ_ID,SEND_DAY,OPER_TYPE,PACKAGE_NAME,SEND_NUM,RECV_SUCCESS_NUM,INSERT_TIME) " +
                    "select SEQ_ID,SEND_DAY,OPER_TYPE,PACKAGE_NAME,SEND_NUM,RECV_SUCCESS_NUM,INSERT_TIME " +
                    "from TB_STATIS_SENDCLEAR_INTF " +
                    "where INSERT_TIME>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and INSERT_TIME<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ");
            Integer count = 0;
            count = DBUtils.execute(connPre, sql.toString());
            XxlJobLogger.log("[普通]本次从中台共同步(条):" + count);

            count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常][普通]更新记录时间异常");
                throw new Exception("[普通]更新记录时间异常");
            }
            XxlJobLogger.log("[普通]更新记录时间为:" + dbCurTime);

            String sqlPro = "MERGE INTO TB_STATIS_TRANS_ETCEEXIT A USING (" +
                    "  select send_day,sum(send_num) as SEND_CNT,sum(recv_success_num) as RECV_CNT " +
                    "  from TB_STATIS_SENDCLEAR_INTF_TMP where oper_type='104' " +
                    "  group by send_day " +
                    ") TMP " +
                    "ON (A.SEND_DAY=TMP.send_day) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.SEND_CNT=A.SEND_CNT+TMP.SEND_CNT,A.RECV_CNT=A.RECV_CNT+TMP.RECV_CNT,A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.SEQ_ID,A.SEND_DAY,A.SEND_CNT,A.RECV_CNT,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_TRANS_ETCEEXIT.NEXTVAL,TMP.send_day,TMP.SEND_CNT,TMP.RECV_CNT,sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]本次共混合ETC出口发送接收统计数据(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_TRANS_EEXIT A USING (" +
                    "  select send_day,sum(send_num) as SEND_CNT,sum(recv_success_num) as RECV_CNT " +
                    "  from TB_STATIS_SENDCLEAR_INTF_TMP where oper_type='102' " +
                    "  group by send_day " +
                    ") TMP " +
                    "ON (A.SEND_DAY=TMP.send_day) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.SEND_CNT=A.SEND_CNT+TMP.SEND_CNT,A.RECV_CNT=A.RECV_CNT+TMP.RECV_CNT,A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.SEQ_ID,A.SEND_DAY,A.SEND_CNT,A.RECV_CNT,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_TRANS_ETCEEXIT.NEXTVAL,TMP.send_day,TMP.SEND_CNT,TMP.RECV_CNT,sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]本次共混合现金出口发送接收统计数据(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_TRANS_GRANTRY A USING (" +
                    "  select send_day,sum(send_num) as SEND_CNT,sum(recv_success_num) as RECV_CNT " +
                    "  from TB_STATIS_SENDCLEAR_INTF_TMP where oper_type='113' " +
                    "  group by send_day " +
                    ") TMP " +
                    "ON (A.SEND_DAY=TMP.send_day) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.SEND_CNT=A.SEND_CNT+TMP.SEND_CNT,A.RECV_CNT=A.RECV_CNT+TMP.RECV_CNT,A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.SEQ_ID,A.SEND_DAY,A.SEND_CNT,A.RECV_CNT,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_TRANS_ETCEEXIT.NEXTVAL,TMP.send_day,TMP.SEND_CNT,TMP.RECV_CNT,sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]本次共混合门架发送接收统计数据(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_TRANS_TOCUST A USING (" +
                    "  select send_day,sum(send_num) as SEND_CNT,sum(recv_success_num) as RECV_CNT " +
                    "  from TB_STATIS_SENDCLEAR_INTF_TMP where oper_type='114' " +
                    "  group by send_day " +
                    ") TMP " +
                    "ON (A.SEND_DAY=TMP.send_day) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.SEND_CNT=A.SEND_CNT+TMP.SEND_CNT,A.RECV_CNT=A.RECV_CNT+TMP.RECV_CNT,A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.SEQ_ID,A.SEND_DAY,A.SEND_CNT,A.RECV_CNT,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_TRANS_ETCEEXIT.NEXTVAL,TMP.send_day,TMP.SEND_CNT,TMP.RECV_CNT,sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]本次共混合清分到发行方统计数据(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_TRANS_TOCLEAR A USING (" +
                    "  select send_day,sum(send_num) as SEND_CNT,sum(recv_success_num) as RECV_CNT " +
                    "  from TB_STATIS_SENDCLEAR_INTF_TMP where oper_type='115' " +
                    "  group by send_day " +
                    ") TMP " +
                    "ON (A.SEND_DAY=TMP.send_day) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.SEND_CNT=A.SEND_CNT+TMP.SEND_CNT,A.RECV_CNT=A.RECV_CNT+TMP.RECV_CNT,A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.SEQ_ID,A.SEND_DAY,A.SEND_CNT,A.RECV_CNT,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_TRANS_ETCEEXIT.NEXTVAL,TMP.send_day,TMP.SEND_CNT,TMP.RECV_CNT,sysdate,sysdate) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]本次共混合发行方到清分统计数据(条):" + count);

            sqlPro = "delete from TB_STATIS_SENDCLEAR_INTF_TMP";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]清除临时表INTF(条):"+count);

            connPre.commit();

            sqlPro = "truncate table TB_STATIS_SENDCLEAR_INTF_TMP";
            DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]重置临时表成功");

            connPre.commit();
            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}
